﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;


using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.GCGBWHOutputDTSTableAdapters;

namespace BusinessLogicLayer
{
    public class GCGBWHOutputBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.GCGBWHOutput; } }

        private UserInformation userOrganization;

        private GCGBWHOutputMaster gcgbWHOutputMaster;

        private BindingList<GCGBWHOutputCollection> gcgbWHOutputCollectionList;

        private bool ReadonlyMode { get; set; }

        public GCGBWHOutputBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                gcgbWHOutputMaster = new GCGBWHOutputMaster();

                this.gcgbWHOutputCollectionList = new BindingList<GCGBWHOutputCollection>();

                GlobalDefaultValue.Apply(this);


                this.GCGBWHOutputMaster.PropertyChanged += new PropertyChangedEventHandler(GCGBWHOutputMaster_PropertyChanged);

                this.GCGBWHOutputCollectionList.ListChanged += new ListChangedEventHandler(GCGBWHOutputCollection_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void GCGBWHOutputMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (!this.ReadonlyMode && (e.PropertyName == "GCGBCommandID"))
            {
                this.StopTracking();
                this.GCGBWHOutputGetDetail();
                this.StartTracking();
            }

            this.SetDirty();
        }

        private void GCGBWHOutputCollection_ListChanged(object sender, ListChangedEventArgs e)
        {

            this.SetDirty();
        }




        #region <Adapter>

        private GCGBWHOutputListingTableAdapter listingTableAdapter;
        protected GCGBWHOutputListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new GCGBWHOutputListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private GCGBWHOutputMasterTableAdapter masterTableAdapter;
        protected GCGBWHOutputMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new GCGBWHOutputMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        private GCGBWHOutputCollectionTableAdapter collectionTableAdapter;
        protected GCGBWHOutputCollectionTableAdapter CollectionTableAdapter
        {
            get
            {
                if (collectionTableAdapter == null) collectionTableAdapter = new GCGBWHOutputCollectionTableAdapter();
                return collectionTableAdapter;
            }
        }

        private GCGBWHOutputCollectionGetDetailTableAdapter collectionGetDetailTableAdapter;
        protected GCGBWHOutputCollectionGetDetailTableAdapter CollectionGetDetailTableAdapter
        {
            get
            {
                if (collectionGetDetailTableAdapter == null) collectionGetDetailTableAdapter = new GCGBWHOutputCollectionGetDetailTableAdapter();
                return collectionGetDetailTableAdapter;
            }
        }

        private GCGBWHOutputDetailTableAdapter detailTableAdapter;
        protected GCGBWHOutputDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new GCGBWHOutputDetailTableAdapter();
                return detailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public GCGBWHOutputDTS.GCGBWHOutputListingDataTable GCGBWHOutputListing(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public GCGBWHOutputMaster GCGBWHOutputMaster
        {
            get { return this.gcgbWHOutputMaster; }
        }

        public BindingList<GCGBWHOutputCollection> GCGBWHOutputCollectionList
        {
            get { return this.gcgbWHOutputCollectionList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int GCGBWHOutputID   //Primary Key
        {
            get { return this.GCGBWHOutputMaster.GCGBWHOutputID; }
            private set
            {
                if (this.GCGBWHOutputMaster.GCGBWHOutputID != value)
                {
                    this.StopTracking();

                    this.GCGBWHOutputMaster.GCGBWHOutputID = value;

                    this.GCGBWHOutputGetMaster();
                    this.GCGBWHOutputGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }



        #endregion <Primary Key>

        private void GCGBWHOutputGetMaster()
        {
            if (this.GCGBWHOutputID > 0)
            {
                GCGBWHOutputDTS.GCGBWHOutputMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.GCGBWHOutputID);

                if (masterDataTable.Count > 0)
                {
                    this.GCGBWHOutputMaster.StopTracking();

                    this.GCGBWHOutputMaster.GCGBCommandID = masterDataTable[0].GCGBCommandID;
                    this.GCGBWHOutputMaster.StockMaterialID = masterDataTable[0].StockMaterialID;
                    this.GCGBWHOutputMaster.GCGBWHOutputDate = masterDataTable[0].GCGBWHOutputDate;
                    this.GCGBWHOutputMaster.EntryDate = masterDataTable[0].EntryDate;
                    this.GCGBWHOutputMaster.GCGBWHOutputReference = masterDataTable[0].GCGBWHOutputReference;
                    this.GCGBWHOutputMaster.PreparedEmployeeID = masterDataTable[0].PreparedEmployeeID;
                    this.GCGBWHOutputMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;
                    this.GCGBWHOutputMaster.Remarks = masterDataTable[0].Remarks;

                    this.GCGBWHOutputMaster.StartTracking();

                    this.GCGBWHOutputMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.GCGBWHOutputMaster);
                this.GCGBWHOutputMaster.GCGBWHOutputDate = DateTime.Today;
                this.GCGBWHOutputMaster.EntryDate = DateTime.Today;
                this.GCGBWHOutputMaster.Reset();
            }
        }


        private void GCGBWHOutputGetDetail()
        {
            this.gcgbWHOutputCollectionList.RaiseListChangedEvents = false;
            this.gcgbWHOutputCollectionList.Clear();
            if (this.GCGBWHOutputID >= 0)
            {
                if (this.GCGBWHOutputMaster.GCGBCommandID > 0)//Fix this, because it run too slow
                {
                    GCGBWHOutputDTS.GCGBWHOutputCollectionGetDetailDataTable collectionGetDetailDataTable = this.CollectionGetDetailTableAdapter.GetData(this.GCGBWHOutputID, this.GCGBWHOutputMaster.GCGBCommandID, this.ReadonlyMode);

                    if (collectionGetDetailDataTable.Rows.Count > 0)
                    {
                        int gcgbCommandID = 0; int gcgbCommandSerialID = 0;
                        foreach (GCGBWHOutputDTS.GCGBWHOutputCollectionGetDetailRow collectionGetDetailRow in collectionGetDetailDataTable.Rows)
                        {
                            if (gcgbCommandID != collectionGetDetailRow.GCGBCommandID || gcgbCommandSerialID != collectionGetDetailRow.GCGBCommandSerialID)
                            {
                                gcgbCommandID = collectionGetDetailRow.GCGBCommandID; gcgbCommandSerialID = collectionGetDetailRow.GCGBCommandSerialID;
                                this.gcgbWHOutputCollectionList.Add(new GCGBWHOutputCollection(collectionGetDetailRow.GCGBCommandSerialID, collectionGetDetailRow.MaterialSetID, collectionGetDetailRow.GCGBCommandQuantity, collectionGetDetailRow.RemainsQuantity, collectionGetDetailRow.Quantity, collectionGetDetailRow.QuantityLoss,
                                        collectionGetDetailRow.MaterialSemiID1, collectionGetDetailRow.QuantityPerSet1, collectionGetDetailRow.QuantitySemiBad1, collectionGetDetailRow.QuantitySemiLoss1,
                                        collectionGetDetailRow.MaterialSemiID2, collectionGetDetailRow.QuantityPerSet2, collectionGetDetailRow.QuantitySemiBad2, collectionGetDetailRow.QuantitySemiLoss2,
                                        collectionGetDetailRow.MaterialSemiID3, collectionGetDetailRow.QuantityPerSet3, collectionGetDetailRow.QuantitySemiBad3, collectionGetDetailRow.QuantitySemiLoss3, collectionGetDetailRow.Remarks));
                            }
                        }
                    }
                }

            }
            this.gcgbWHOutputCollectionList.RaiseListChangedEvents = true;
            this.gcgbWHOutputCollectionList.ResetBindings();
        }




        public double TotalQuantity
        {
            get { return this.gcgbWHOutputCollectionList.Sum(q => q.Quantity); }
        }

        public double TotalQuantitySemi1
        {
            get { return this.gcgbWHOutputCollectionList.Sum(q => q.QuantitySemi1); }
        }

        public double TotalQuantitySemi2
        {
            get { return this.gcgbWHOutputCollectionList.Sum(q => q.QuantitySemi2); }
        }

        public double TotalQuantitySemi3
        {
            get { return this.gcgbWHOutputCollectionList.Sum(q => q.QuantitySemi3); }
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<GCGBWHOutputCollection> inValidGCGBWHOutputCollection = this.GCGBWHOutputCollectionList.Where(gcgbPlasterCollection => !gcgbPlasterCollection.IsValid).ToList();
                return this.GCGBWHOutputMaster.IsValid && inValidGCGBWHOutputCollection.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.gcgbWHOutputMaster.GCGBWHOutputDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("GCGBWHOutputApproved", this.GCGBWHOutputID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBWHOutputEditable", this.GCGBWHOutputID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.gcgbWHOutputMaster.GCGBWHOutputDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBWHOutputVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBWHOutputEditable", this.GCGBWHOutputID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.gcgbWHOutputMaster.GCGBWHOutputDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBWHOutputUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBWHOutputEditable", this.GCGBWHOutputID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int gcgbWHOutputID)
        {
            this.ReadonlyMode = true;
            if (this.GCGBWHOutputID == gcgbWHOutputID) this.GCGBWHOutputID = -1; //Enforce to reload
            this.GCGBWHOutputID = gcgbWHOutputID;
        }

        public void New()
        {
            this.ReadonlyMode = false;
            if (this.GCGBWHOutputID == 0) this.GCGBWHOutputID = -1;
            this.GCGBWHOutputID = 0;
        }

        public void Edit()
        {
            this.ReadonlyMode = false;

            int gcgbWHOutputID = this.GCGBWHOutputID;

            this.GCGBWHOutputID = -1;
            this.GCGBWHOutputID = gcgbWHOutputID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int gcgbCommandID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref gcgbCommandID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(gcgbCommandID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    
                }

                this.Fill(gcgbCommandID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.gcgbWHOutputMaster.GCGBWHOutputID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.gcgbWHOutputMaster.GCGBWHOutputID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.gcgbWHOutputMaster.GCGBWHOutputID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.GCGBWHOutputMaster.PreparedEmployeeID, this.GCGBWHOutputMaster.GCGBWHOutputDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });


            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int gcgbWHOutputID)
        {
            GCGBWHOutputDTS.GCGBWHOutputMasterDataTable masterDataTable;
            GCGBWHOutputDTS.GCGBWHOutputMasterRow masterRow;

            if (this.gcgbWHOutputMaster.GCGBWHOutputID <= 0) //Add
            {
                masterDataTable = new GCGBWHOutputDTS.GCGBWHOutputMasterDataTable();
                masterRow = masterDataTable.NewGCGBWHOutputMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(gcgbWHOutputMaster.GCGBWHOutputID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(gcgbWHOutputMaster.GCGBWHOutputID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.StockMaterialID = this.gcgbWHOutputMaster.StockMaterialID;
            masterRow.GCGBCommandID = this.gcgbWHOutputMaster.GCGBCommandID;
            masterRow.GCGBWHOutputDate = this.gcgbWHOutputMaster.GCGBWHOutputDate;
            masterRow.EntryDate = this.gcgbWHOutputMaster.EntryDate;
            masterRow.GCGBWHOutputReference = this.gcgbWHOutputMaster.GCGBWHOutputReference;
            masterRow.PreparedEmployeeID = this.gcgbWHOutputMaster.PreparedEmployeeID;
            masterRow.ApprovedEmployeeID = this.gcgbWHOutputMaster.ApprovedEmployeeID;
            masterRow.Remarks = this.gcgbWHOutputMaster.Remarks;
            masterRow.GCGBCommandReference = this.gcgbWHOutputMaster.GCGBCommandReference;
            masterRow.GCGBCommandDate = this.gcgbWHOutputMaster.GCGBCommandDate;

            masterRow.Approved = false;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.gcgbWHOutputMaster.GCGBWHOutputID <= 0) masterDataTable.AddGCGBWHOutputMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            gcgbWHOutputID = masterRow.GCGBWHOutputID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int gcgbWHOutputID)
        {
            int serialID = 0; int rowsAffected = 0; int rowsOutput = 0;


            #region <Save collection>

            serialID = 0;

            GCGBWHOutputDTS.GCGBWHOutputCollectionDataTable collectionDataTable = new GCGBWHOutputDTS.GCGBWHOutputCollectionDataTable();
            GCGBWHOutputDTS.GCGBWHOutputDetailDataTable detailDataTable = new GCGBWHOutputDTS.GCGBWHOutputDetailDataTable();

            foreach (GCGBWHOutputCollection gcgbWHOutputCollection in this.gcgbWHOutputCollectionList)
            {
                if (gcgbWHOutputCollection.Quantity > 0 || gcgbWHOutputCollection.QuantityLoss > 0)
                {
                    rowsOutput++;

                    GCGBWHOutputDTS.GCGBWHOutputCollectionRow collectionRow = collectionDataTable.NewGCGBWHOutputCollectionRow();

                    collectionRow.GCGBWHOutputID = gcgbWHOutputID;
                    collectionRow.SerialID = ++serialID;

                    collectionRow.StockMaterialID = this.GCGBWHOutputMaster.StockMaterialID;
                    collectionRow.GCGBWHOutputDate = this.GCGBWHOutputMaster.GCGBWHOutputDate;
                    collectionRow.GCGBWHOutputReference = this.GCGBWHOutputMaster.GCGBWHOutputReference;

                    collectionRow.GCGBCommandID = this.GCGBWHOutputMaster.GCGBCommandID;
                    collectionRow.GCGBCommandSerialID = gcgbWHOutputCollection.GCGBCommandSerialID;
                    
                    collectionRow.MaterialSetID = gcgbWHOutputCollection.MaterialSetID;
                    collectionRow.Quantity = gcgbWHOutputCollection.Quantity;
                    collectionRow.QuantityLoss = gcgbWHOutputCollection.QuantityLoss;

                    collectionRow.MaterialSemiID1 = gcgbWHOutputCollection.MaterialSemiID1;
                    collectionRow.QuantityPerSet1 = gcgbWHOutputCollection.QuantityPerSet1;
                    collectionRow.QuantitySemi1 = gcgbWHOutputCollection.QuantitySemi1;
                    collectionRow.QuantitySemiBad1 = gcgbWHOutputCollection.QuantitySemiBad1;
                    collectionRow.QuantitySemiLoss1 = gcgbWHOutputCollection.QuantitySemiLoss1;

                    collectionRow.MaterialSemiID2 = gcgbWHOutputCollection.MaterialSemiID2;
                    collectionRow.QuantityPerSet2 = gcgbWHOutputCollection.QuantityPerSet2;
                    collectionRow.QuantitySemi2 = gcgbWHOutputCollection.QuantitySemi2;
                    collectionRow.QuantitySemiBad2 = gcgbWHOutputCollection.QuantitySemiBad2;
                    collectionRow.QuantitySemiLoss2 = gcgbWHOutputCollection.QuantitySemiLoss2;

                    collectionRow.MaterialSemiID3 = gcgbWHOutputCollection.MaterialSemiID3;
                    collectionRow.QuantityPerSet3 = gcgbWHOutputCollection.QuantityPerSet3;
                    collectionRow.QuantitySemi3 = gcgbWHOutputCollection.QuantitySemi3;
                    collectionRow.QuantitySemiBad3 = gcgbWHOutputCollection.QuantitySemiBad3;
                    collectionRow.QuantitySemiLoss3 = gcgbWHOutputCollection.QuantitySemiLoss3;


                    collectionRow.Remarks = gcgbWHOutputCollection.Remarks;

                    collectionRow.Approved = false;
                    collectionRow.InActive = false;

                    collectionDataTable.AddGCGBWHOutputCollectionRow(collectionRow);
                }
            }

            rowsAffected = this.CollectionTableAdapter.Update(collectionDataTable);
            if (rowsAffected != rowsOutput) throw new System.ArgumentException("Insufficient save", "Save detail: GCGBWHOutputCollection");

            int rowsUpdated = this.CollectionTableAdapter.GCGBWHOutputCollectionSaveUpdate(gcgbWHOutputID, (int)GlobalMsADO.UpdateOption.SaveUpdate);

            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int gcgbWHOutputID)
        {
            int rowsUpdated = this.CollectionTableAdapter.GCGBWHOutputCollectionSaveUpdate(gcgbWHOutputID, (int)GlobalMsADO.UpdateOption.SaveUndo);
            int rowsDeleted = this.CollectionTableAdapter.Delete(gcgbWHOutputID);

            if (rowsUpdated > rowsDeleted) throw new System.ArgumentException("Insufficient save", "Save undo Collection");

            return true;
        }


        private bool SaveConflict()
        {
            return false;
        }



        #endregion



        #region Import Excel

        public bool ImportExcel(string fileName)
        {
            try
            {
                DateTime dateTimeValue; string gcgbWHOutputReference = "";
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                TimeSpan timeout = TimeSpan.FromMinutes(90);
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, timeout))
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict");

                    DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.GCGBCommand, fileName);
                    if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                    {
                        string importGCGBWHOutputID = "";
                        GCGBWHOutputBLL importGCGBWHOutputBLL = new GCGBWHOutputBLL();

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (importGCGBWHOutputID != excelDataRow["GCGBWHOutputID"].ToString().Trim())
                            {//Start a new importGCGBCommandBLL
                                importGCGBWHOutputID = excelDataRow["GCGBWHOutputID"].ToString().Trim();

                                //Save current importGCGBCommandBLL if existing
                                if (!exceptionTable.IsDirty)
                                    if (importGCGBWHOutputBLL.IsDirty && !importGCGBWHOutputBLL.Save())
                                        exceptionTable.AddException(new string[] { "Insufficient save", gcgbWHOutputReference });

                                //Start a new importGCGBCommandBLL
                                importGCGBWHOutputBLL.New();
                                exceptionTable.ClearDirty();



                                //ERmgrVCP.ListGCGBCommandTypeRow gcgbCommandTypeRow = CommonDatabaseMethod.GetMarketingPaymentType(0, excelDataRow["MarketingPaymentTypeID"].ToString().Trim());
                                //if (gcgbCommandTypeRow != null)
                                //    importGCGBCommandBLL.gcgbCommandMaster.GCGBCommandTypeID = gcgbCommandTypeRow.GCGBCommandTypeID;
                                //else
                                //{//Handle exception when MarketingPaymentType can not be found
                                //    exceptionTable.AddException(new string[] { "Payment Type", excelDataRow["GCGBCommandTypeID"].ToString() });
                                //}


                                if (DateTime.TryParse(excelDataRow["GCGBWHOutputDate"].ToString(), out dateTimeValue)) importGCGBWHOutputBLL.gcgbWHOutputMaster.GCGBWHOutputDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["RequestedDate"].ToString() });
                                if (DateTime.TryParse(excelDataRow["EntryDate"].ToString(), out dateTimeValue)) importGCGBWHOutputBLL.gcgbWHOutputMaster.EntryDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["EntryDate"].ToString() });

                                importGCGBWHOutputBLL.gcgbWHOutputMaster.GCGBWHOutputReference = excelDataRow["GCGBCommandReference"].ToString();

                                ERmgrVCP.SPGetSalesmenNameRow salesmenNameRow = CommonDatabaseMethod.GetSalesmenName(0, excelDataRow["PreparedEmployeeID"].ToString().Trim());
                                if (salesmenNameRow != null)
                                    importGCGBWHOutputBLL.gcgbWHOutputMaster.PreparedEmployeeID = salesmenNameRow.SalesmenID;
                                else
                                {//Handle exception when ItemType can not be found
                                    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["PreparedEmployeeID"].ToString() });
                                }


                                salesmenNameRow = CommonDatabaseMethod.GetSalesmenName(0, excelDataRow["ApprovedEmployeeID"].ToString().Trim());
                                if (salesmenNameRow != null)
                                    importGCGBWHOutputBLL.gcgbWHOutputMaster.ApprovedEmployeeID = salesmenNameRow.SalesmenID;
                                else
                                {//Handle exception when ItemType can not be found
                                    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["ApprovedEmployeeID"].ToString() });
                                }

                                importGCGBWHOutputBLL.gcgbWHOutputMaster.Remarks = excelDataRow["Remarks"].ToString();
                            }

                            #region Import Detail Collection

                            ERmgrVCP.SPGetCustomerNameRow customerNameRow = CommonDatabaseMethod.GetCustomerName(0, excelDataRow["CustomerID"].ToString().Trim());
                            if (customerNameRow != null)
                            {
                                ERmgrVCP.SPGetMarketingPaymentTermRow marketingPaymentTermRow = CommonDatabaseMethod.GetMarketingPaymentTerm(0, excelDataRow["MarketingPaymentTermID"].ToString().Trim());
                                if (marketingPaymentTermRow != null)
                                    importGCGBWHOutputBLL.GCGBWHOutputCollectionList.Add(new GCGBWHOutputCollection(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, excelDataRow["DetailRemarks"].ToString().Trim()));
                                //importGCGBWHOutputBLL.GCGBWHOutputCollectionList.Add(new GCGBWHOutputCollection(customerNameRow.CustomerID, 0, marketingPaymentTermRow.MarketingPaymentTermID, (double)excelDataRow["TurnOverAmount"], (double)excelDataRow["IncentiveAmount"], 0, 0, 0, 0, 0, 0, excelDataRow["DetailRemarks"].ToString().Trim()));
                                else
                                {//Handle exception when MarketingPaymentTerm can not be found
                                    exceptionTable.AddException(new string[] { "Payment Term", excelDataRow["MarketingPaymentTermID"].ToString() });
                                }
                            }

                            else
                            {//Handle exception when CustomerName can not be found
                                exceptionTable.AddException(new string[] { "Customer", excelDataRow["CustomerID"].ToString() });
                            }

                            #endregion Import Detail Collection

                        }

                        //Save the last importGCGBCommandBLL after loop for all excelDataRow
                        if (!exceptionTable.IsDirty)
                            if (importGCGBWHOutputBLL.IsDirty && !importGCGBWHOutputBLL.Save())
                                exceptionTable.AddException(new string[] { "Insufficient save", gcgbWHOutputReference });
                        //--------------OK

                    }
                    if (exceptionTable.Table.Rows.Count <= 0)
                        transactionScope.Complete();
                    else
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);

                }
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }

        }

        #endregion Import Excel



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      LEFT(DATENAME(Month, GCGBWHOutputMaster.GCGBWHOutputDate), 3) + ', ' + DATENAME(Year, GCGBWHOutputMaster.GCGBWHOutputDate) AS Month, GCGBWHOutputID, GCGBWHOutputMaster.GCGBWHOutputDate, GCGBWHOutputMaster.GCGBWHOutputReference, GCGBWHOutputMaster.GCGBCommandID, GCGBCommandMaster.GCGBCommandReference, GCGBWHOutputMaster.StockMaterialID, GCGBWHOutputMaster.EntryDate, GCGBWHOutputMaster.PreparedEmployeeID, GCGBWHOutputMaster.ApprovedEmployeeID, GCGBWHOutputMaster.Remarks, GCGBWHOutputMaster.UserID, GCGBWHOutputMaster.UserOrganizationID, GCGBWHOutputMaster.Approved " + "\r\n";
            queryString = queryString + "       FROM        GCGBWHOutputMaster INNER JOIN " + "\r\n";
            queryString = queryString + "            GCGBCommandMaster ON GCGBWHOutputMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID " + "\r\n";
            queryString = queryString + "       WHERE       GCGBWHOutputMaster.GCGBWHOutputDate >= @LowerFillterDate AND GCGBWHOutputMaster.GCGBWHOutputDate <= @UpperFillterDate " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputListing", queryString);





            queryString = "     @GCGBWHOutputID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      GCGBWHOutputMaster.GCGBWHOutputID, GCGBWHOutputMaster.GCGBWHOutputDate, GCGBWHOutputMaster.GCGBWHOutputReference, GCGBWHOutputMaster.GCGBCommandID, GCGBWHOutputMaster.StockMaterialID, GCGBWHOutputMaster.EntryDate, GCGBWHOutputMaster.PreparedEmployeeID, GCGBWHOutputMaster.ApprovedEmployeeID, GCGBWHOutputMaster.Remarks, GCGBWHOutputMaster.UserID, GCGBWHOutputMaster.UserOrganizationID, GCGBWHOutputMaster.Approved, GCGBCommandMaster.GCGBCommandReference, GCGBCommandMaster.GCGBCommandDate " + "\r\n";
            queryString = queryString + "       FROM        GCGBWHOutputMaster INNER JOIN " + "\r\n";
            queryString = queryString + "                   GCGBCommandMaster ON GCGBWHOutputMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID " + "\r\n";
            queryString = queryString + "       WHERE       GCGBWHOutputMaster.GCGBWHOutputID = @GCGBWHOutputID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputMasterSelect", queryString);


            queryString = "     @GCGBWHOutputID int, @GCGBWHOutputDate datetime, @GCGBWHOutputReference nvarchar(50), @GCGBCommandID int, @StockMaterialID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBWHOutputMaster (GCGBWHOutputDate, GCGBWHOutputReference, GCGBCommandID, StockMaterialID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Remarks, UserID, UserOrganizationID, Approved) VALUES (@GCGBWHOutputDate, @GCGBWHOutputReference, @GCGBCommandID, @StockMaterialID, @EntryDate, @PreparedEmployeeID, @ApprovedEmployeeID, @Remarks, @UserID, @UserOrganizationID, @Approved) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBWHOutputID, GCGBWHOutputDate, GCGBWHOutputReference, GCGBCommandID, StockMaterialID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Remarks, UserID, UserOrganizationID, Approved FROM GCGBWHOutputMaster WHERE GCGBWHOutputID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputMasterInsert", queryString);


            queryString = " @GCGBWHOutputID int, @GCGBWHOutputDate datetime, @GCGBWHOutputReference nvarchar(50), @GCGBCommandID int, @StockMaterialID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      GCGBWHOutputMaster SET GCGBWHOutputDate = @GCGBWHOutputDate, GCGBWHOutputReference = @GCGBWHOutputReference, GCGBCommandID = @GCGBCommandID, StockMaterialID = @StockMaterialID, EntryDate = @EntryDate, PreparedEmployeeID = @PreparedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, Remarks = @Remarks, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Approved = @Approved WHERE GCGBWHOutputID = @GCGBWHOutputID " + "\r\n";
            queryString = queryString + "       SELECT      GCGBWHOutputID, GCGBWHOutputDate, GCGBWHOutputReference, GCGBCommandID, StockMaterialID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Remarks, UserID, UserOrganizationID, Approved FROM GCGBWHOutputMaster WHERE GCGBWHOutputID = @GCGBWHOutputID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputMasterUpdate", queryString);


            queryString = " @GCGBWHOutputID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM GCGBWHOutputMaster WHERE GCGBWHOutputID = @GCGBWHOutputID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBWHOutputMasterDelete", queryString);




            queryString = " @GCGBWHOutputID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      GCGBWHOutputID, SerialID, GCGBWHOutputReference, GCGBWHOutputDate, GCGBCommandID, GCGBCommandSerialID, StockMaterialID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, Remarks, Approved, InActive " + "\r\n";
            queryString = queryString + "       FROM        GCGBWHOutputCollection " + "\r\n";
            queryString = queryString + "       WHERE       GCGBWHOutputID = @GCGBWHOutputID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputCollectionSelect", queryString);


            queryString = " @GCGBWHOutputID int, @SerialID int, @GCGBWHOutputReference nvarchar(50), @GCGBWHOutputDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @StockMaterialID int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO GCGBWHOutputCollection (GCGBWHOutputID, SerialID, GCGBWHOutputReference, GCGBWHOutputDate, GCGBCommandID, GCGBCommandSerialID, StockMaterialID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, Remarks, Approved, InActive) VALUES (@GCGBWHOutputID, @SerialID, @GCGBWHOutputReference, @GCGBWHOutputDate, @GCGBCommandID, @GCGBCommandSerialID, @StockMaterialID, @MaterialSetID, @Quantity, @QuantityLoss, @MaterialSemiID1, @QuantityPerSet1, @QuantitySemi1, @QuantitySemiBad1, @QuantitySemiLoss1, @MaterialSemiID2, @QuantityPerSet2, @QuantitySemi2, @QuantitySemiBad2, @QuantitySemiLoss2, @MaterialSemiID3, @QuantityPerSet3, @QuantitySemi3, @QuantitySemiBad3, @QuantitySemiLoss3, @Remarks, @Approved, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT      GCGBWHOutputID, SerialID, GCGBWHOutputReference, GCGBWHOutputDate, GCGBCommandID, GCGBCommandSerialID, StockMaterialID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, Remarks, Approved, InActive FROM GCGBWHOutputCollection WHERE GCGBWHOutputID = @GCGBWHOutputID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputCollectionInsert", queryString);


            queryString = " @GCGBWHOutputID int, @SerialID int, @GCGBWHOutputReference nvarchar(50), @GCGBWHOutputDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @StockMaterialID int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      GCGBWHOutputCollection SET GCGBWHOutputID = @GCGBWHOutputID, SerialID = @SerialID, GCGBWHOutputReference = @GCGBWHOutputReference, GCGBWHOutputDate = @GCGBWHOutputDate, GCGBCommandID = @GCGBCommandID, GCGBCommandSerialID = @GCGBCommandSerialID, StockMaterialID = @StockMaterialID, MaterialSetID = @MaterialSetID, Quantity = @Quantity, QuantityLoss = @QuantityLoss, MaterialSemiID1 = @MaterialSemiID1, QuantityPerSet1 = @QuantityPerSet1, QuantitySemi1 = @QuantitySemi1, QuantitySemiBad1 = @QuantitySemiBad1, QuantitySemiLoss1 = @QuantitySemiLoss1, MaterialSemiID2 = @MaterialSemiID2, QuantityPerSet2 = @QuantityPerSet2, QuantitySemi2 = @QuantitySemi2, QuantitySemiBad2 = @QuantitySemiBad2, QuantitySemiLoss2 = @QuantitySemiLoss2, MaterialSemiID3 = @MaterialSemiID3, QuantityPerSet3 = @QuantityPerSet3, QuantitySemi3 = @QuantitySemi3, QuantitySemiBad3 = @QuantitySemiBad3, QuantitySemiLoss3 = @QuantitySemiLoss3, Remarks = @Remarks, Approved = @Approved, InActive = @InActive  WHERE GCGBWHOutputID = @GCGBWHOutputID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      GCGBWHOutputID, SerialID, GCGBWHOutputReference, GCGBWHOutputDate, GCGBCommandID, GCGBCommandSerialID, StockMaterialID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, Remarks, Approved, InActive FROM GCGBWHOutputCollection WHERE GCGBWHOutputID = @GCGBWHOutputID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputCollectionUpdate", queryString);


            queryString = " @GCGBWHOutputID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM GCGBWHOutputCollection WHERE GCGBWHOutputID = @GCGBWHOutputID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBWHOutputCollectionDelete", queryString);


            string queryEdit; string queryNew;

            queryEdit = "               SELECT          GCGBCommandCollection.GCGBCommandID AS GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandDate AS GCGBCommandDate, " + "\r\n";
            queryEdit = queryEdit + "                   GCGBCommandCollection.MaterialSetID, GCGBCommandCollection.Quantity AS GCGBCommandQuantity, ROUND(GCGBCommandCollection.Quantity - GCGBCommandCollection.QuantityWHOutput - GCGBCommandCollection.QuantityWHOutputLoss + GCGBWHOutputCollection.Quantity + GCGBWHOutputCollection.QuantityLoss, 0) AS RemainsQuantity, GCGBWHOutputCollection.Quantity, GCGBWHOutputCollection.QuantityLoss, " + "\r\n";
            queryEdit = queryEdit + "                   GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, GCGBWHOutputCollection.QuantitySemiBad1, GCGBWHOutputCollection.QuantitySemiLoss1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, GCGBWHOutputCollection.QuantitySemiBad2, GCGBWHOutputCollection.QuantitySemiLoss2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, GCGBWHOutputCollection.QuantitySemiBad3, GCGBWHOutputCollection.QuantitySemiLoss3, GCGBCommandCollection.Remarks, 0 AS RSC  " + "\r\n";
            queryEdit = queryEdit + "   FROM            GCGBCommandMaster INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   GCGBCommandCollection ON GCGBCommandMaster.GCGBCommandID = GCGBCommandCollection.GCGBCommandID INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   GCGBWHOutputCollection ON GCGBCommandCollection.GCGBCommandID = GCGBWHOutputCollection.GCGBCommandID AND GCGBCommandCollection.SerialID = GCGBWHOutputCollection.GCGBCommandSerialID " + "\r\n";
            queryEdit = queryEdit + "   WHERE           (@GCGBCommandID <= 0 OR GCGBCommandCollection.GCGBCommandID = @GCGBCommandID) AND GCGBWHOutputCollection.GCGBWHOutputID = @GCGBWHOutputID " + "\r\n";

            queryNew = "                SELECT          GCGBCommandCollection.GCGBCommandID AS GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandDate AS GCGBCommandDate, " + "\r\n";
            queryNew = queryNew + "                     GCGBCommandCollection.MaterialSetID, GCGBCommandCollection.Quantity AS GCGBCommandQuantity, ROUND(GCGBCommandCollection.Quantity - GCGBCommandCollection.QuantityWHOutput - GCGBCommandCollection.QuantityWHOutputLoss, 0) AS RemainsQuantity, 0 AS Quantity, 0 AS QuantityLoss, " + "\r\n";
            queryNew = queryNew + "                     GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, 0 AS QuantitySemiBad1, 0 AS QuantitySemiLoss1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, 0 AS QuantitySemiBad2, 0 AS QuantitySemiLoss2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, 0 AS QuantitySemiBad3, 0 AS QuantitySemiLoss3, GCGBCommandCollection.Remarks, 1 AS RSC " + "\r\n";
            queryNew = queryNew + "     FROM            GCGBCommandMaster INNER JOIN " + "\r\n";
            queryNew = queryNew + "                     GCGBCommandCollection ON GCGBCommandMaster.GCGBCommandID = GCGBCommandCollection.GCGBCommandID " + "\r\n";
            queryNew = queryNew + "     WHERE           (@GCGBCommandID <= 0 OR GCGBCommandCollection.GCGBCommandID = @GCGBCommandID)  AND GCGBCommandCollection.Approved = 1 AND (ROUND(GCGBCommandCollection.Quantity - GCGBCommandCollection.QuantityWHOutput, 0) > 0) " + "\r\n";//AND MarketingIncentiveMaster.Approved = 1 

            queryString = " @GCGBWHOutputID Int, @GCGBCommandID Int, @ReadonlyMode bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + " IF (@GCGBWHOutputID <= 0) " + "\r\n";
            queryString = queryString + "    BEGIN " + "\r\n";
            queryString = queryString + "       " + queryNew + "\r\n";
            queryString = queryString + "        ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "    END " + "\r\n";
            queryString = queryString + " ELSE " + "\r\n";

            queryString = queryString + "   IF (@ReadonlyMode = 1) " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";
            queryString = queryString + "   ELSE " + "\r\n"; //FULL SELECT FOR EDIT MODE

            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           UNION ALL " + "\r\n";
            queryString = queryString + "           " + queryNew + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID, RSC " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBWHOutputCollectionGetDetail", queryString);

            queryString = " @GCGBWHOutputID int, @UpdateOption int " + "\r\n"; //UpdateOption: 1 Savedetail, -1:Saveundo
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBCommandCollection " + "\r\n";
            queryString = queryString + "       SET             GCGBCommandCollection.QuantityWHOutput = ROUND(GCGBCommandCollection.QuantityWHOutput + GCGBWHOutputCollection.Quantity * @UpdateOption, 0), GCGBCommandCollection.QuantityWHOutputLoss = ROUND(GCGBCommandCollection.QuantityWHOutputLoss + GCGBWHOutputCollection.QuantityLoss * @UpdateOption, 0), " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection.QuantitySemiWHOutputBad1 = ROUND(GCGBCommandCollection.QuantitySemiWHOutputBad1 + GCGBWHOutputCollection.QuantitySemiBad1 * @UpdateOption, 0), GCGBCommandCollection.QuantitySemiWHOutputLoss1 = ROUND(GCGBCommandCollection.QuantitySemiWHOutputLoss1 + GCGBWHOutputCollection.QuantitySemiLoss1 * @UpdateOption, 0), " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection.QuantitySemiWHOutputBad2 = ROUND(GCGBCommandCollection.QuantitySemiWHOutputBad2 + GCGBWHOutputCollection.QuantitySemiBad2 * @UpdateOption, 0), GCGBCommandCollection.QuantitySemiWHOutputLoss2 = ROUND(GCGBCommandCollection.QuantitySemiWHOutputLoss2 + GCGBWHOutputCollection.QuantitySemiLoss2 * @UpdateOption, 0), " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection.QuantitySemiWHOutputBad3 = ROUND(GCGBCommandCollection.QuantitySemiWHOutputBad3 + GCGBWHOutputCollection.QuantitySemiBad3 * @UpdateOption, 0), GCGBCommandCollection.QuantitySemiWHOutputLoss3 = ROUND(GCGBCommandCollection.QuantitySemiWHOutputLoss3 + GCGBWHOutputCollection.QuantitySemiLoss3 * @UpdateOption, 0) " + "\r\n";
            queryString = queryString + "       FROM            GCGBWHOutputCollection INNER JOIN " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection ON GCGBWHOutputCollection.GCGBWHOutputID = @GCGBWHOutputID AND GCGBWHOutputCollection.GCGBCommandID = GCGBCommandCollection.GCGBCommandID AND GCGBWHOutputCollection.GCGBCommandSerialID = GCGBCommandCollection.SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBWHOutputCollectionSaveUpdate", queryString);


            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 GCGBWHOutputID FROM GCGBWHOutputMaster WHERE GCGBWHOutputID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBWHOutputApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];
            //queryArray[0] = " SELECT TOP 1 MarketingIncentiveID FROM MarketingPaymentMaster WHERE MarketingIncentiveID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBWHOutputEditable", queryArray);

        }



    }
}
